package com.usian.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

public class POIUtil{

	/**
     * 读取07版excel数据，即.xlsx文件
     *
     * @param inputStream
     * @return
     */
    public static List<Map<String, Object>> readExcelData07(InputStream inputStream) {
        List<Map<String, Object>> result = new ArrayList<>();
        Map<Integer, String> titleIndex = new HashMap<>();
        // 1、获取文件流
        // 2、创建工作簿 Workbook，03版使用HSSFWrokbook
        try (
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            // 3、获取第一个sheet
            Sheet sheet = workbook.getSheetAt(0);
            // 4、遍历获取标题内容
            Row rowTitle = sheet.getRow(0);
            if (rowTitle != null) {
                // 获取列数
                int columns = rowTitle.getPhysicalNumberOfCells();
                for (int i = 0; i < columns; i++) {
                    Cell cell = rowTitle.getCell(i);
                    if (cell != null) {
                        String title = cell.getStringCellValue();
                        //log.info("标题:{}", title);
                        // 将标题对应的title放入map中
                        titleIndex.put(i, title);
                    }
                }
            }
            // 5、遍历获取表中内容
            // 总行数
            int rowNumbers = sheet.getPhysicalNumberOfRows();
            //log.info("总行数:{}", rowNumbers);
            // 第一行是标题，所以从第二行开始读取数据，索引从0开始，读取到后放入到Map中
            for (int i = 1; i < rowNumbers; i++) {
                Map<String, Object> data = new HashMap<>();
                result.add(data);
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                // 获取总列数
                int columnNumbers = row.getPhysicalNumberOfCells();
                // 遍历每一个单元格内容
                for (int j = 0; j < columnNumbers; j++) {
                    Cell cell = row.getCell(j);
                    String title = titleIndex.get(j);
                    if (cell == null) {
                        continue;
                    }
                    // 判断类型
                    CellType cellType = cell.getCellType();
                    String cellValue = "";
                    switch (cellType) {
                        case STRING: // 字符串
                            System.out.print("【String】");
                            cellValue = cell.getStringCellValue();
                            break;
                        case BOOLEAN: // 布尔
                            System.out.print("【BOOLEAN】");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case BLANK: // 空
                            System.out.print("【BLANK】");
                            break;
                        case NUMERIC: // 数字（日期、普通数字）
                            System.out.print("【NUMERIC】");
                            if (DateUtil.isCellDateFormatted(cell)) { // 日期
                                Date date = cell.getDateCellValue();
                                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HHmm:ss");
                                cellValue = format.format(date);
                                System.out.print("【日期】");
                            } else {
                                System.out.print("【数字类型】");
                                // 将数字格式化为String
                                DataFormatter dataFormatter = new DataFormatter();
                                cellValue = dataFormatter.formatCellValue(cell);
                            }
                            break;
                        case ERROR:
                            System.out.print("【数据类型错误】");
                            break;
                    }
                    data.put(title, cellValue);
                    //log.info("读取到excel内容，全部转为String类型:{}", cellValue);
                }
            }
        } catch (Exception e) {
            //log.error("读取excel异常", e);
        }
        return result;
    }
	
	
	
	
	public static void createExcel(List<Map<String, String>> data, String[] titles, String sheetName, String fileName, HttpServletResponse response) throws IOException {
        //2、创建一个XSSFWorkbook，对应一个Excel文件
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            //3. 创建表格格式CellStyle，设置文字垂直居中对齐，水平左对齐
            XSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.LEFT);
            //文本垂直居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            // 4、在workbook中添加一个sheet,对应Excel文件中的sheet
            XSSFSheet sheet = workbook.createSheet(sheetName);
            // 标题
            //声明列对象
            XSSFCell cell;
            // 	5. 标题写入第一行数据，第0行,
            XSSFRow row = sheet.createRow(0);
            for (int i = 0; i < titles.length; i++) {
                //在第一行创建第i格单元格
                cell = row.createCell(i, CellType.STRING);
                // 设置样式为居中
                cell.setCellStyle(style);
                cell.setCellValue(titles[i]);
            }

            // 6. 循环写入列表数据
            for (int i = 0; i < data.size(); i++) {
                Map<String, String> stringObjectMap = data.get(i);
                XSSFRow dataRow = sheet.createRow(i + 1);
                for (int j = 0; j < titles.length; j++) {
                    //在第j行创建第i格单元格
                    cell = dataRow.createCell(j, CellType.STRING);
                    // 设置样式为居中
                    cell.setCellStyle(style);
                    cell.setCellValue(stringObjectMap.get(titles[j]));
                }
            }
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("Connection", "close");
            response.setHeader("Content-Type", "application/octet-stream");
            workbook.write(response.getOutputStream());
        }

    }
}